
import mysql2 from 'mysql2'
import md5 from 'md5'
import cache from './cache'
import mysqlmodule from './model'
import fs from 'fs';
import path from 'path'
import tool from './tool'
import Judge from './Judge'
import fun from './sqlfun'

import { limit, offset, orderBy, orWhere, table, where } from './sqlfun';

interface option{
    type: string;
    table:string[];
    sql_arr:Array<Record<string,string>>;
    param:any[];
    field:string[];
    sql?:string;
    lifeCycle?:{start?:Function,medi?:Function;end?:Function}
}

async function start (data?:any):Promise<{
    start_model:Function;
    life:Function;
    table(tab:string|Array<string>,li:object,...t):table;
    select(SQL:any,Param):any;
}>{
    let s = new mysql(data);
    await s.start();
    return s;
}

class mysql{
    // 保存数据库的的配置信息
    private config_Data = undefined;
    //sql语句缓存
    private cache_sql = {};
    //Pool数据库连接池，DB数据库操作，Table表结构数据，有表的字段，主键，以及表名
    public Pool:any;
    private TABLE:Record<string,any> = {};

    //==========================================原生sql操作，一切封装都是依托这个方法
    public async select(option:option|string|any){
        if(typeof option == 'string'){option = {sql:option,table:[],type:"cgem"}}
        option.lifeCycle?.medi && option.lifeCycle.medi(option);//执行结束生命周期
        //查询缓存|清除缓存
        switch (option.type){
            case "select":{
                //获取缓存
                let data= cache.get(option.sql+option.param,option.table);
                if(data){return data}
                break;
            }
            case "insert"://清除缓存
            case "update"://清除缓存
            case "delete":{ cache.delete(option.table);}
        }
        // console.log(this)
        return new Promise((resolve,rej)=>{
            //调用生命周期函数
            this.Pool.query(option.sql,option.param,(err:Error,res:any)=>{
                if(err){
                    rej(err);
                }else{
                    //添加缓存
                    cache.set(option.table,option.sql+option.param,res)
                    //执行sql语句返回成功插入的数据的数量
                    if(option.lifeCycle?.end){
                        resolve( option.lifeCycle.end(res) || res)
                    }else{
                        resolve(res);
                    }
                }
            });
        });
    }

    /**
     * 静态启动
     * @param data 
     * @returns 
     */

    // 构造方法
    constructor(data?:any){
        if(!data){
            // 查找配置文件，找到了就进行连接
            if(fs.existsSync(path.join(process.cwd(),"mysql.config.json"))){
                console.log("开始连接");
                data = require(path.join(process.cwd(),"mysql.config.json"));
            }else{
                throw new Error("请提供数据库配置")
            }
        }
        this.config_Data =data;
        // 获取所有的表信息 放入缓存
        // @ts-ignore
        this.life.life_Cycle = undefined;
    }
    // 执行初始化
    async start(){
        this.Pool = await connection(this.config_Data);
        this.TABLE = await tool.getAll_Table(this,this.config_Data.database);
        Object.keys(this.TABLE).forEach(e=>cache.setTable(e))
    }

    /**
     * 启动模型
     * @param data 
     */
    public async start_model (data?:any){
        if(this.Pool instanceof Promise) this.Pool = await this.Pool;
        // 先初始换
        this['model'] = await mysqlmodule(this,this.TABLE);
    };

    /**
     * 声明生命周期
     * @param create 
     * @returns 
     */
    public life(create:{start?:Function,medi?:Function,end?:Function}){
        this.life['life_Cycle']=create;
        return this;
    }
    
    /**
     * 每一次操作必然是要走table()，选择控制表,此方法每次查询只能使用一次且必须调用
     * @param tab 
     * @param li 
     * @param t 
     * @returns 
     */
    public table(tab:string|Array<string>,li:object,...t):table{
        let DB = this;

        //=================================================一级封装，基础操作，其他封装都依托一以下封装的方法
        class methodsCollection{
            /**
             * 解析where参数
             */
            private static pars_where_param(field:any,jud:any,term:any,many={left:"",right:""}){
                // term是无效数据吗
                if(Judge.isInvalid(term)){
                    term=jud; jud="=";
                    if(!Array.isArray(term) ) term = [term]
                }else{
                    // 如果是对象不是数组，说明term是延申数据
                    if(typeof term === 'object' && !Array.isArray(term)){
                        many =term; 
                        Array.isArray(term)?(term=jud):(term=[jud]); 
                        jud="=";
                    }else if(!Array.isArray(term)){
                        term = [term]                        
                    }
                }
                many.left = many.left?many.left+" ":"";
                many.right = many.right?" "+many.right:"";
                // 解析好的数据进行返回
                return {field,jud,term,many}
            }
            // 判断条件，field为字段,jud为判断表达式，可以不填，默认为=，不填时jud=term，term=查询的数据，可以为数组
            static where(field:any,jud:any,term:any,many={left:"",right:""}):where{
                let param =methodsCollection.pars_where_param(field,jud,term,many);
                field = param.field;
                jud = param.jud;
                term = param.term;
                many = param.many;
                let sql = "",end = "";
                term.forEach((element:any) => {
                    //      or ( `id` = ? )
                    sql+=`${end}${many.left}\`${field}\` ${jud} ?${many.right}`;
                    end=" or ";
                    Param.push(element)
                });
                Sql_Array.push({where:sql});
                return Fun.where;
            }
        
            //or判断
            static orWhere(field:any,jud:any,term:any,many={left:"",right:""}):orWhere{
                let param =methodsCollection.pars_where_param(field,jud,term,many);
                field = param.field;
                jud = param.jud;
                term = param.term;
                many = param.many;
                let sql = "",end = "";
                term.forEach((element:any) => {
                    sql+=`${end}${many.left}\`${field}\` ${jud} ?${many.right}`;
                    end="or";
                    Param.push(element)
                });
                Sql_Array.push({orWhere:sql});
                return Fun.orWhere;
            }
        
            //on的与 此方法不参与预编译
            static onn(field:any,jud:any,term:any):where{
                let param =methodsCollection.pars_where_param(field,jud,term);
                field = param.field;
                jud = param.jud;
                term = param.term;
                let sql = "",end = " ";
                term.forEach((element:any) => {
                    //      or ( `id` = ? )
                    sql+=`${end} \`${field}\` ${jud} ? `;
                    end="or";
                    Param.push(element)
                });
                Sql_Array.push({on:sql});
                return Fun.where;
            }
        
            //on的或方法 此方法不参与预编译
            static oron(field:any,jud:any,term:any):where{
                let param =methodsCollection.pars_where_param(field,jud,term);
                field = param.field;
                jud = param.jud;
                term = param.term;
                
                let sql = "",end = " ";
                term.forEach((element:any) => {
                    //      or ( `id` = ? )
                    sql+=`${end} \`${field}\` ${jud} ? `;
                    end="or";
                    Param.push(element)
                });
                Sql_Array.push({orWhere:sql});
                return Fun.where;
            }
        
            //此方法参与预编译
            static on(field:any,jud:any,term?:any):where{
                let param =methodsCollection.pars_where_param(field,jud,term);
                field = param.field;
                jud = param.jud;
                term = param.term;
                
                let sql = "",end = "";
                term.forEach((element:any) => {
                    sql+=`${end} ${field} ${jud} ${element}`;
                    end=" and ";
                });
                Sql_Array.push({on:sql});
                return Fun.where;
            }
            
        
            //此方法参与预编译
            static onWhere(field,jud,term):where{
                let param =methodsCollection.pars_where_param(field,jud,term);
                field = param.field;
                jud = param.jud;
                term = param.term;

                let sql = "",end = "";
                term.forEach(element => {
                    sql+=end+"`"+field+"`"+jud+element;end=" and ";
                });
                Sql_Array.push({where:sql});
                return Fun.where;
            }
        
            
            //多添加进sql语句的东西
            static many(data="("):table{
                Sql_Array.push({many:data});
                return this;
            }
        
            //排序
            static orderBy(param:string,desc=""):orderBy{
                // desc 是降序
                Sql_Array.push({orderBy:param+" "+desc});
                return Fun.orderBy;
            }
        
        
            //分页 获取几条数据
            static limit(param:number|string):limit{
                Sql_Array.push({limit:'limit '+param});
                return Fun.limit;
            }
        
            //分页 跳过几条数据
            static offset(param:number) :offset{
                Sql_Array.push({offset:'offset '+param});
                return Fun.offset;
            }
        
            //返回结果,返回输出结果
            static async get(...field):Promise<Array<object>>{
                if(field.length === 0) field.push("*")
                let sql = JSON.stringify(Sql_Array);
                // 查看sql缓存
                let option = DB.config_Data.cache?DB.cache_sql[sql]:undefined || methodsCollection.createSql({
                    type:'select',
                    sql_arr:Sql_Array,
                    table:Table,
                    field:Array.isArray(field[0])?field[0]:field,
                    param:Param,
                    lifeCycle
                });

                DB.cache_sql[sql] || (DB.cache_sql[sql] = option)
                return await DB.select(option);  
            }
        
            static async value(data:any):Promise<any>{
                let a = await this.first();
                return !Judge.isInvalid(a)?a[data]:undefined;
            }
        
            //给外面看的sql语句
            static toSql(type:string = "select",data:any = "*"):string{
                let option = methodsCollection.createSql({
                    type,
                    sql_arr:Sql_Array,
                    table:Table,
                    field:data,
                    param:Param
                });
                option.param.forEach(e=>{option.sql = option.sql.replace(/\?/,e)})
                return option.sql;
            }
        
            //获取一条数据
            static async first(...params:any[]) :Promise<object>{
                let data = await methodsCollection.limit(1).get(...params);
                return data.length>0?data[0]:undefined;
            }
        
            //分页功能
            static paging(page=1,number=10):limit{
                return methodsCollection.limit((page-1)*number+","+number);
            }
            
            //修改操作
            static async update(data):Promise<number>{
                let option = methodsCollection.createSql({
                    type:"update",
                    sql_arr:Sql_Array,
                    table:Table,
                    field:data,
                    param:Param,
                    lifeCycle
                });
                return (await DB.select(option)).affectedRows;
            }
        
            //删除操作
            static async delete() :Promise<number>{
                let option = methodsCollection.createSql({
                    type:"delete",
                    sql_arr:Sql_Array,
                    table:Table,
                    field:["*"],
                    param:Param,
                });
                return (await DB.select(option)).affectedRows;
            }
        
            //添加数据
            static async insert(param):Promise<void>{
                //生成sql语句以及提取数据，返回是对象{sql:"",data:[]}
                let option = methodsCollection.createSql({
                    type:"insert",
                    sql_arr:Sql_Array,
                    table:Table,
                    field:param,
                    param:Param,
                    lifeCycle
                });
               
                // 查询数据
                return (await DB.select(option)).affectedRows;  
            }
        
            static async insertId(param):Promise<number>{
                //生成sql语句以及提取数据，返回是对象{sql:"",data:[]}
                let option = methodsCollection.createSql({
                    type:"insert",
                    sql_arr:Sql_Array,
                    table:Table,
                    field:param,
                    param:Param,
                    lifeCycle
                });
                
                // 查询数据
                return (await DB.select(option)).affectedRows;  
            }
        
            //默认自减，第二个参数为需要减的数
            static async decrement(parameter,number=1):Promise<number>{
                let sql = 'update '+Table+' set '+parameter+"="+parameter+"-"+number+" "+methodsCollection.toCondition(Sql_Array);
                // 查询数据
                return (await DB.select({sql,table:Table,type:'update',lifeCycle})).affectedRows;  
            }
            
            //默认自增，第二个参数为需要减的数
            static async increment(parameter,number=1):Promise<number>{
                let sql = 'update '+Table+' set '+parameter+"="+parameter+"+"+number+" "+methodsCollection.toCondition(Sql_Array);
                return (await DB.select({
                    type:"update",
                    sql_arr:Sql_Array,
                    table:Table,
                    field:parameter,
                    param:Param,
                    sql,
                    lifeCycle
                })).affectedRows;
            }
        
            //count计算数据
            static async count(field=["*"]):Promise<any>{
                let fie = await methodsCollection.first(field.map(res=>`count(${res})`));
                fie = field.reduce((p,res)=>{
                    p[res] = fie[`count(${res})`];
                    return p;
                },{})
                if(field.length==1)return fie[field[0]];
                return fie;
            }
        //=======================二级封装，基于一级封装的基础上进行实现的功能
            static noGet(data:any):Promise<any>{
                data =Array.isArray(data)?data:[data];
                //获取表名并去除空格
                if(!DB.TABLE[Table[0]]?.table_field) throw new Error("请先调用start()")
                let field = DB.TABLE[Table[0]]?.table_field;
                //数组合并
                field = !Judge.isInvalid(data)?field.concat(data):field;
                //去除重复项
                field = Judge.removeDuplicate(field);
                //调用get
                return methodsCollection.get(field);
            }
        
            static noFirst(data:any):Promise<any>{
                data =Array.isArray(data)?data:[data];
                if(!DB.TABLE[Table[0]]?.table_field) throw new Error("请先调用start()")
                //获取表名并去除空格
                let field =  DB.TABLE[Table[0]].table_field;
                //数组合并
                field = !Judge.isInvalid(data)?field.concat(data):field;
                //去除重复项
                field = Judge.removeDuplicate(field);
        
                //调用get
                return methodsCollection.first(field);
            }
        
        
        //==========================================================连表查询
            /*单纯的join是内连接，拥有二个参数，参数1右表，参数二表达式 */
            static join(table:any,bd:any):where{
                Sql_Array.push({join:{table:table,bd:bd}});
                return Fun.where;
            }
        
            /**左连接 */
            static join_left(table:any,bd:any):where{
                Sql_Array.push({join_left:{table:table,bd:bd}});
                return Fun.where;
            }
        
            /**右连接 */
            static join_right(table:any,bd:any):where{
                Sql_Array.push({join_right:{table:table,bd:bd}});
                return Fun.where;
            }

            //生成中间的sql语句，不生成sql头语句
            static createSql(option:option):option{
                //查询sql语句是否存在
                let sql = DB.cache_sql[md5(JSON.stringify(option))];
                if(sql){return option.sql=sql,option;}
                let fun = {
                    insert:this.toInsert,
                    select:this.toSelect,
                    update:this.toUpdate,
                    delete:this.toDelete,
                }
                //执行操作,执行对应的方法,sql为sql片段的数组，field为数据或字段部分，如查询就是字段，插入就是数据
                fun[option.type](option)
                DB.cache_sql[md5(JSON.stringify(option))]=option;
                return option;
            }

            //生成插入sql语句
            static toInsert(option:option):option{
                option.sql = "insert into `"+option.table.join("`")+"` (";
                Array.isArray(option.field) || (option.field = [option.field])
                option.field.forEach((e:any)=>{for(let a in e){option.param.push(e[a])}})
                //获取字段
                option.sql += "`"+Object.keys(option.field[0]).join("`,`")+"`) values";
                let wh =  Object.keys(option.field[0]).map(a=>{return "?";});
                // 获取所有的value,以及问号
                wh = option.field.map(res =>"("+wh+")");
                //去除最后的逗号
                option.sql += wh;
                return option;
            }
            //生成查询sql语句
            static toSelect(option:option):option{
                //重构get，添加因数，顺序
                option.sql ='select '+option.field.join(",")+' from `'+Table.join("`")+"` ";
                return option.sql+=methodsCollection.toCondition(option.sql_arr),option;
            }

            static toDelete(option:option):option{
                //删除语句
                option.sql = "delete `"+Table.join("`")+"` from `"+Table.join("`")+"` ";
                //生成条件查询语句
                option.sql+=methodsCollection.toCondition(option.sql_arr);
                return option;
            }

            //生成修改的sql语句
            static toUpdate(option:option):option{
                let para = [];
                option.sql = "update `"+option.table.join("`")+"` set ";
                //修改后的数据必须用''包裹不然报错，且id主键不可改
                option.sql += Object.keys(option.field).reduce((par,value)=>{
                    par.push(value+" = ?");
                    para.push(option.field[value])
                    return par;
                },[])+" ";
                option.param = para.concat(option.param);
                //生成条件查询语句
                option.sql+=methodsCollection.toCondition(option.sql_arr);
                return option;
            }
            static getUpdate(option:option):option{
                return option;
            }
            
            //获取条件语句，如where，orWhere等中部的语句，提供给select，update使用
            static toCondition(sql_arr:Array<Record<string,string>>):string{
                //整体和尾部
                let SQL = "",tail_sql="";
                let toDealWith = {where,orWhere,on,orderBy,limit,offset,join,join_left,join_right,many}
                sql_arr.forEach(obj=>{
                    Object.keys(obj).forEach(key=>{
                        toDealWith[key]?.(obj[key]);
                    });
                });
                return SQL;

                //===============================解析的方法
                function where(data){
                    //判断是不是第一个where判断
                    SQL += SQL.includes('where ') || tail_sql=="and"?("and "+data+" "):("where "+data+" ");
                }
                function orWhere(data){
                    SQL += SQL.includes('where ') || tail_sql=="and"?("or "+ data+" "):("where " + data+" ");
                }
                function on(data){
                    //判断是不是第一个where判断,并且前面是不是表名
                    SQL += SQL.includes('where ') || tail_sql=="and"?("and " + data+" "):("on "+data+" ");
                }
                function orderBy(data){
                    //判断一下前面有没有,
                    SQL += SQL.includes("ORDER BY")?(","+data+" "):("ORDER BY "+data+" ");
                }
                function limit(data){
                    SQL+= data+" ";
                }
                function offset(data){
                    SQL+= data+" ";
                }
                function join(data){
                    // bd是表达式
                    //进行内连接
                    SQL+='join '+data.table+' ';
                    if(data.bd){
                        SQL+='on '+data.bd+' ';tail_sql="and";
                    }
                }
                function join_left(data){
                    //进行左连接
                    SQL+='left join '+data.table+' ';
                    if(data.bd){
                        SQL+='on '+data.bd+' ';tail_sql="and";
                    }
                }
                function join_right(data){
                    //进行右连接
                    SQL+='right join '+data.table+' ';
                    if(data.bd){
                        SQL+='on '+data.bd+' ';tail_sql="and";
                    }
                }
                function many(data){
                    SQL+= data+" ";
                }
            }
        }
        const Fun = fun(methodsCollection)
        
        //sql语句的顺序,Param是数据，防止sql注入的，也就是被？代替的部分
        let Sql_Array=[],Param=[],Table=Array.isArray(tab)?tab:[tab];
         if(typeof li !== 'object'){
            if(li)
            Table = Table.concat([li]).concat(t)
         }else{
            this.life['life_Cycle'] = li;
         }
        //生命周期
        let lifeCycle = this.life['life_Cycle'] || {start(){},medi(){},end(){}}
        lifeCycle.start && lifeCycle.start();
        return Fun.table;
    //=========================================查询()结尾
    }

    

    
}
if(module) module.exports = {start,mysql}
export {start,mysql}
export default mysql;

// export function a(){}

//连接数据库
function connection(config_Data){
    return new Promise(async (res,err)=>{
        try{
            if(!config_Data) throw new Error("请先配置数据库")
            if(!config_Data.host)throw new Error("缺少host")
            if(!config_Data.user)throw new Error("缺少user")
            if(!config_Data.password)throw new Error("缺少password")
            if(!config_Data.database)throw new Error("缺少database")
            let Pool = mysql2.createPool({
                host     : config_Data.host,
                user     : config_Data.user,
                password : config_Data.password,
                database : config_Data.database,
                port:config_Data.port || 3306,
                waitForConnections: true,
                connectionLimit: 10,
                queueLimit: 0
            });
            // 创建一次连接然后关闭，用来测试数据库是否连接正常
            (await Pool.promise().getConnection()).release();
            console.log("mysql连接成功");
            //开始初始化，获取全部表结构数据
            res(Pool)
        }catch(e){
            err(e)
        }
    })
}